﻿USE [Cycles to Share Version1];              --use this specific database
go

--Drop all tables to start with a new clean DB
PRINT 'Dropping all tables!';                       --print warning statement 

--Drop the old AccountSub Tables
DROP TABLE IF EXISTS AccountSub  --drop table
PRINT 'AccountSub has been Dropped'

-- Drop the Old City table
DROP TABLE IF EXISTS City  --drop table
PRINT 'City Table Dropped';

 -- Drop the Old Station table
DROP TABLE IF EXISTS Station  --drop table
PRINT 'Station Table Dropped';

-- Drop the Old Account table
DROP TABLE IF EXISTS Account  --drop table
PRINT 'Account Table Dropped';

-- Drop the Old Bike table
DROP TABLE IF EXISTS Bike   --drop table
PRINT 'Bike Table Dropped';

-- Drop the Old Rental table 
DROP TABLE IF EXISTS Rental   --drop table
PRINT 'Rental Table Dropped' ;

-- Drop the Old RentalBike table  
DROP TABLE IF EXISTS RentalBike   --drop table
PRINT 'rentalBike Table Dropped';

-- Drop the Old Station table  
DROP TABLE IF EXISTS Station   --drop table
PRINT 'Station Table Dropped';

-- Drop the Old TarrifRate table 
DROP TABLE IF EXISTS TarrifRate   --drop table
PRINT 'TarrifRate Table Dropped';

-- Drop the old PaymentMethods
DROP TABLE IF EXISTS PaymentMethods   --drop table
print 'PaymentsMenthods Table dropped' 
go

PRINT 'all tables have been dropped'
PRINT 'Will Start on creating the tables for the new database now'
go

--set privileges                                --print message
print 'No privileges have been set,!!!!'        --remind the DBA to set the privileges to users that are required to access the PaymentsMenthods Table
print 'Set some!!'
go

--Create the Country Table

CREATE TABLE City(                                              --create Table for City
	CityCode int IDENTITY(1,1) PRIMARY KEY not null,            --create an field called CityCode that will take an Int, and be the primary key for the table, will not take a null value
	CityName nchar(50) not null,                                --create field called CityName that will take a nchar value,and will not allow a null value
	CityCounty nchar(20) not null,                              --create a field called CityCounty that takes a nchar value, and will not allow a null value
	CityRegion nchar(20) not null,                              --create field called CityRegion that will take a nchar value,and will not allow a null value
);

PRINT 'Created the City Table'                                  --print statement on completion
 
--Create the Station Table

CREATE TABLE Station(                                           --create a table called Station
	StationCode int IDENTITY(1,1) PRIMARY KEY not null,         --create an field called StationCode that will take an Int, and be the primary key for the table, will not take a null value
	StationName nchar(50) not null UNIQUE,                      --create field called StationName that will take a nchar value,and will not allow a null value, and the value entered must be unique
	StationAddress nchar(20) not null,                          --create field called StationAddress that will take a nchar value,and will not allow a null value
	StationCityCode int not null FOREIGN KEY REFERENCES City (CityCode), --create field called StationCityCode that will take a int value,and will not allow a null value, FK references CityCode, in the city table
	StationPostcode nchar(10) not null,                         --create field called StationPostcode that will take a nchar value,and will not allow a null value
	StationPhoneNumber int not null,                            --create field called StationPhoneNumber that will take a Int value,and will not allow a null value
	StationReginalStation nchar(20) not null,                   --create field called StationReginalStation that will take a nchar value,and will not allow a null value
);
PRINT 'Created the Station Table'                               --print statement on completion

CREATE TABLE Account( --create a table called account
	AccountNumber int IDENTITY(1,1) PRIMARY KEY not not null,  --create field called AccountNumber that will take a int value,and will not allow a null value, and is the primary key for the account table
	RegistrationDate Date not null,                            --create field called RegistrationDate that will take a date value,and will not allow a null value
	FirstName nchar(20) not null,                              --create field called FirstName that will take a nchar value,and will not allow a null value
	LastNam nchar(20) not null,                                --create field called LastNam that will take a nchar value,and will not allow a null value
	AccountDateOfBirth nchar(20) not null,                     --create field called AccountDateOfBirth that will take a nchar value,and will not allow a null value
	AccountEmail nchar(20) NOT NULL UNIQUE,                    --create field called AccountEmail that will take a nchar value,and will not allow a null value and must also be unique
	AccountPhoneNumber int32 not null,                         --create field called AccountPhoneNumber that will take a int32 value,and will not allow a null value
	AccountAddress nchar(20) not null FOREIGN KEY REFERENCES Station(StationAddress), --create field called AccountAddress that will take a nchar value,and will not allow a null value, FK references StationAddress on the Station table
	AccountCityCode nchar(20) not null,                        --create field called AccountCityCode that will take a nchar value,and will not allow a null value
	AccountPostcode nchar(9) not null,                         --create field called AccountPostcode that will take a nchar value,and will not allow a null value
	AccountBikeNu int check (AccountBikeNu >= 6)               --create a check constraint on the AccountBikeNu no entry for any value above 6
);

PRINT 'Created the Account Table'                              --print statement on completion

CREATE TABLE PaymentMethods(                                   --create table called Payment methods
	AccountNumber int IDENTITY (1,1) FOREIGN KEY REFERENCES Account(AccountNumber), --create field called CityName that will take a int value,and will not allow a null value, FK references AccountNumber on the Accounts Table
	AccountPaymentCard nchar(30) NOT NULL,                     --create field called AccountPaymentCard that will take a nchar value,and will not allow a null value
	AccountPaymentCardCVC nchar (30) NOT NULL,                 --create field called AccountPaymentCardCVC that will take a nchar value,and will not allow a null value
	AccountPassword nchar(20) not null,                        --create field called AccountPassword that will take a nchar value,and will not allow a null value
	AccountPaymentDate Date not null,                          --create field called AccountPaymentDate that will take a nchar value,and will not allow a null value
); 
PRINT 'PaymentMethods Table Created'                           --print statement on completion


--Create the Bike Table

CREATE TABLE Bike(                                             --create a table called Bike
	BikeCode int IDENTITY(1,1) PRIMARY KEY not null,           --create field called BikeCode that will take a Int value,and will not allow a null value
	BikeStationCode nchar(20) null FOREIGN KEY REFERENCES StationID(Station), --create field called BikeStationCode that will take a nchar value,and will not allow a null value, FK references StationID on the Station table
--Boolean for bike status, ie Available or not
	BikeSatus bit not null,                                    --create field called BikeSatus that will take a Bit value,and will not allow a null value, to allow for Y/N on bike hire status
	BikeComments nchar(50) null,                               --create field called BikeComments that will take a nchar large value,and will allow a null value to allow comments
	
);
PRINT 'Created the Bike Table'                                 --print statement on completion

CREATE TABLE Rental(                                           --create a table called Rental
	RentalNum int IDENTITY(1,1) PRIMARY KEY not null,          --create field called rentalNum that will take a int value,and will not allow a null value
	RentalAccountNum nchar(10) null FOREIGN KEY REFERENCES AccountNumber(Account), --create field called RentalAccountNum that will take a nchar value,and will not allow a null value, FK references AccountNumber on the Account tables
	RentalBookingDateTime datetime not null,                   --create field called RentalBookingDateTime that will take a Datetime value,and will not allow a null value
	RentalPickupStation nchar(10) NOT NULL,                    --create field called RentalPickupStation that will take a nchar value,and will not allow a null value, fk references StationCode on the station Table
	RentalLockcode nchar(10) NOT NULL UNIQUE,                  --create field called RentalLockcode that will take a nchar value,and will not allow a null value and must be a unique code for that specific bike
	
);
PRINT 'Created the Rental Table'                                --print statement on completion

CREATE TABLE RentalBike                                         --create a table called RentalBike
(
	RentalBikeNum int  IDENTITY(1,1) PRIMARY KEY not null,      --create field called RentalBikeNum that will take a int value,and will not allow a null value and is the primary key for this table
	RentalBikeCode int, --FOREIGN KEY REFERENCES bikeCode(bike),--create field called RentalBikeCode that will take a int value,and will not allow a null value. fk references the BikeCode on the bike table
	RentalBikePickup datetime not null,                         --create field called RentalBikePickup that will take a Datetime value,and will not allow a null value
	RentalReturn nchar(50) not null,                            --create field called RentalReturn that will take a nchar value,and will not allow a null value
	RentalReturnStation int, -- FOREIGN KEY REFERENCES StationCode(Station), --create field called RentalReturnStation that will take a nchar value,and will not allow a null value, fk references stationcode on the station table 
	RentalHireCharge int null,                                  --create field called RentalHireCharge that will take a int value,and will  allow a null value
	RentalLostLockCharge bit  null,                             --create field called RentalLostLockCharge that will take a int value,and will allow a null value
	RentalDamageCharge int null,                                --create field called RentalDamageCharge that will take a int value,and will allow a null value
	RentalComments nchar(50) null                               --create field called RentalComments that will take a large nchar value,and will allow a null value
);

PRINT 'Created the RentalBike Table'                            --print statement on completion

CREATE TABLE AccountSub(                                        --create a table called AccountSub
	AccountSubID nchar (20) PRIMARY KEY not null,               --create field called AccountSubID that will take a nchar value,and will not allow a null value, and is the PK of this table
	AccountSubNum int IDENTITY(1,1) FOREIGN KEY REFERENCES Account(AccountNumber) not null, --create field called AccountSubNum that will take a int value, FK references the accountNumber on the account table
	AccountSubStartDate date null,                              --create field called AccountSubStartDate that will take a Date value,and will allow a null value
	AccountSubEndDate date null,                                --create field called AccountSubEndDate that will take a date value,and will allow a null value
	AccountSubType nchar (10) not null,                         --create field called AccountSubType that will take a nchar value,and will not allow a null value
	AccountSubTarrifRate nchar (10) not null,                   --create field called AccountSubTarrifRate that will take a nchar value,and will not allow a null value
	AccountSubStatus nchar (10) not null,                       --create field called AccountSubStatus that will take a nchar value,and will not allow a null value
	
);
PRINT 'Created the AccountSub Table'                            --print statement on completion

CREATE TABLE TarrifRate(                                        --create a table called TarrifRate
	TarrifID nchar (20) PRIMARY KEY not null,                   --create field called TarrifID that will take a nchar value,and will not allow a null value, and acts as the PK of this table
	TarrifSubType nchar (10) not null, FOREIGN KEY REFERENCES AccountSub(AccountSubType), --create field called TarrifSubType that will take a nchar value,and will not allow a null value, FK references the account sub type on the accountsub table
	TarrifSubPrice DECIMAL(8,2) not null,                       --create field called TarrifSubPrice that will take a DECIMAL value,and will not allow a null value
	TarrifFirstHourRate DECIMAL(8,2) not null,                  --create field called TarrifFirstHourRate that will take a DECIMAL value,and will not allow a null value
	TarrifHourlyRate DECIMAL(8,2) not null,                     --create field called TarrifHourlyRate that will take a DECIMAL value,and will not allow a null value
	TarrifMaxDailyRate DECIMAL(8,2) not null,                   --create field called TarrifMaxDailyRate that will take a DECIMAL value,and will not allow a null value
	
);
PRINT 'Created the TarrifRate Table'

Print 'Set up complete'                                         --print statement on completion

